package com.zhangyj.impl.plugin;

import java.io.Writer;
import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Types;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.zhangyj.BoastException;
import com.zhangyj.db.DBUtil;
import com.zhangyj.util.SQLMacro;
import com.zhangyj.util.Tools;

public class JSONBaseDataService implements IDataService {
	Map<String, String> paramMap = new HashMap<String, String>();
	Connection conn = null;
	boolean isSelfOpenConn = false;
	ResultSetMetaData rsmd;
	ResultSet rst;
	PreparedStatement ps;
	SQLMacro sqlMacro;
	int columnsTypes[];
	String[] columnsLabels;
	int idIndex = 1;
	int start = 0;
	int limit = -1;
	boolean hasCountSQL = true;
	public JSONBaseDataService(String sqlName,Connection conn) throws SQLException{
		if (conn == null) {
			this.conn = DBUtil.getConnection();
			isSelfOpenConn = true;
		} else {
			this.conn = conn;
		}
		sqlMacro = SQLMacro.getSQLMacro(sqlName);
		if (sqlMacro == null) {
			throw new BoastException("The SQLMacro can't is null!");
		}
	}

	@Override
	public void addPararam(String key, String value) {
		paramMap.put(key, value);

	}

	@Override
	public void close() throws BoastException {
		try {
			if (rst != null)
				rst.close();
			if (ps != null) {
				ps.close();
			}
			if (isSelfOpenConn) {
				conn.close();
				conn = null;
				isSelfOpenConn = false;
			}
		} catch (SQLException e) {
			throw new BoastException(e);
		}
	}

	@Override
	public void open() throws BoastException {
		try {
			close();
			ps = this.conn.prepareStatement(sqlMacro.getResultSql());
			setParam(ps);
			rst = ps.executeQuery();
			rsmd = rst.getMetaData();

			int cols = rsmd.getColumnCount();
			columnsTypes = new int[cols];
			columnsLabels = new String[cols];
			for (int i = 0; i < cols; i++) {
				columnsLabels[i] = rsmd.getColumnLabel(i+1);
				switch (rsmd.getColumnType(i + 1)) {
				case Types.INTEGER:
					columnsTypes[i] = SQLMacro.TYPE_INT;
				case Types.DECIMAL:
					columnsTypes[i] = SQLMacro.TYPE_DECIMAL;
					break;
				default:
					columnsTypes[i] = SQLMacro.TYPE_STRINT;
				}
				if (this.sqlMacro.getKey() != null && this.sqlMacro.getKey().equals(rsmd.getColumnLabel(i + 1))) {
					this.idIndex = i;
				}
			}
		} catch (SQLException e) {
			throw new BoastException(e);
		}
	}

	private void setParam(PreparedStatement ps) throws SQLException {
		List<SQLMacro.Param> params = sqlMacro.getParams();
		if (params != null) {
			for (int i = 0; i < params.size(); i++) {
				SQLMacro.Param param = params.get(i);
				String val = paramMap.get(param.name);
				if (val == null) {
					val = param.defaultValue;
				}
				switch (param.type) {
				case SQLMacro.TYPE_INT:
					ps.setInt(i + 1, val == null ? 0 : Integer.parseInt(val));
					break;
				case SQLMacro.TYPE_DECIMAL:
					ps.setBigDecimal(i + 1, val == null ? BigDecimal.ZERO : new BigDecimal(val));
					break;
				default:
					ps.setString(i + 1, val);
				}
			}
		}
	}

	@Override
	public void writerTo(Writer writer) throws BoastException {
		try {
			int count = getResCount();
			if (count > 0) {
				start = Tools.str2int(this.paramMap.get("start"),0);
				limit = Tools.str2int(this.paramMap.get("limit"), -1);
			}
			writer.append("{identifier:\"").append(sqlMacro.getKey()).append("\",lable:\"").append(sqlMacro.getKey()).append("\",");
			writer.append("\"items\":");
			int resCount  = writerData(writer);
			if (hasCountSQL) {
				writer.append(",\"total\":").append(count + "");
			} else {
				writer.append(",\"total\":").append(resCount + "");
			}
			writer.append("}");
		} catch (Exception e) {
			throw new BoastException(e);
		}
	}

	private int writerData(Writer writer) throws Exception {
		int resCount = 0;
		writer.append("[");
		int no = -1;
		boolean isAdd = false;
		while (rst.next()) {
			resCount ++;
			no++;
			if (limit != -1) {
				if (start > no) {
					continue;
				}
				if (start + limit <= no) {
					break;
				}
			}
			if (isAdd) {
				writer.append(",");
			} else {
				isAdd = true;
			}
			writer.append("{");
			int i = 0;
			if (columnsTypes.length > 0) {
				writeCellData(i, columnsTypes[i],writer);
				i++;
				for (; i < columnsTypes.length; i++) {
					writer.append(",");
					writeCellData(i, columnsTypes[i],writer);
				}
			}
			writer.append("}");
		}
		writer.append("]");
		return resCount;
	}

	private void writeCellData(int col, int type,Writer writer) throws Exception {
		writer.append("\"").append(columnsLabels[col]).append("\":");
		col++;
		switch (type) {
		case SQLMacro.TYPE_INT:
			writer.append(rst.getInt(col)+"");
			break;
		case SQLMacro.TYPE_DECIMAL:
			BigDecimal decimal = rst.getBigDecimal(col);
			if (decimal == null) {
				writer.append("0.00");
			} else {
				writer.append(decimal.toString());
			}
			break;
		default:
			String val = rst.getString(col);
			if (val == null) {
				writer.append( "null");
			} else {
				val = Tools.builderJSONString(rst.getString(col));
				writer.append(  "\"" + val + "\"");
			}
		}
	}

	private int getResCount() throws SQLException {
		int count = 0;
		String sql = this.sqlMacro.getResCountSQL();
		if (sql != null) {
			PreparedStatement ps = this.conn.prepareStatement(sqlMacro.getResCountSQL());
			setParam(ps);
			ResultSet rst = ps.executeQuery();
			if (rst.next()) {
				count = rst.getInt(1);
			}
			rst.close();
			ps.close();
		} else {
			this.hasCountSQL = false;
		}
		return count;
	}

	public static void main(String[] args) {
		try {
			String a = null;
			System.out.println(a instanceof String);
//			IDataService service = new JSONBaseDataService("clientList");
//			service.open(null);
//			StringWriter writer = new StringWriter();
//			service.writerTo(writer);
//			service.close();
//			System.out.print(writer.toString());
		} catch (Exception e) {
			e.printStackTrace();
		}
	}
}
